<?php
// +----------------------------------------------------------------------
// | INPHP
// | Copyright (c) 2023 https://inphp.cc All rights reserved.
// | Licensed ( https://opensource.org/licenses/MIT )
// | Author: 幺月儿(https://gitee.com/lulanyin) Email: inphp@qq.com
// +----------------------------------------------------------------------
// | SQL 语法处理，该对象仅将语法处理并返回查询语句和参数值，不执行
// +----------------------------------------------------------------------
namespace Inphp\Core\Db\PDO;

use Inphp\Core\Db\Db;

class Grammar
{
    /**
     * 查询对象
     * @var Query
     */
    private Query $query;

    public function __construct(Query $query)
    {
        $this->query = $query;
    }

    /**
     * 编译Query对象为SQL和参数
     * @param string $type
     * @return array
     */
    public function compileQuery(string $type = "select"): array
    {
        //数据库类型，未来可用于判断处理不同的语法
        //$driver = Db::getConnection()->driver;
        $tableName = $this->compileTable();
        switch ($type) {
            case "delete":
                //delete from $tableName $where
                list($where, $params) = $this->compileWhere();
                if(strripos($tableName, " ")){
                    $tableName = substr($tableName, 0, strripos($tableName, " "));
                }
                $queryString = "delete from {$tableName}{$where}";
                break;
            case "update":
                //update $tableName set $columns $where
                list($columns, $params1) = $this->compileSet($type);
                list($where, $params2) = $this->compileWhere();
                if(strripos($tableName, " ")){
                    $tableName = substr($tableName, 0, strripos($tableName, " "));
                }
                $queryString = "update {$tableName} set {$columns}{$where}";
                $params = array_merge($params1, $params2);
                break;
            case "insert":
                //insert into {table name}(columns) values({$values})
                list($columns, $values, $params) = $this->compileSet($type);
                $queryString = [];
                if (strripos($tableName, " ")) {
                    $tableName = substr($tableName, 0, strripos($tableName, " "));
                }
                foreach ($columns as $key => $c) {
                    $queryString[] = "insert into {$tableName} {$c} values {$values[$key]};";
                }
                break;
            case "insert where not exists" :
                //插入数据，当记录不存在时
                //insert into {table name}(columns) select {values} from TEMP1 where not exists(select {table name}{where})
                list($columns, $values, $params1) = $this->compileSet($type);
                list($where, $params2) = $this->compileWhere();// where里边包含了语句和参数
                if(strripos($tableName, " ")){
                    $tableName = substr($tableName, 0, strripos($tableName, " "));
                }
                $queryString = "insert into {$tableName} {$columns} select {$values} from dual where not exists(select * from {$tableName}{$where});";
                $params = array_merge($params1, $params2);
                break;
            case "truncate" :
                //清空数据表
                if(strripos($tableName, " ")){
                    $tableName = substr($tableName, 0, strripos($tableName, " "));
                }
                $queryString = "truncate table {$tableName}";
                $params = [];
                break;
            default:
                //默认查询
                $columns    = $this->compileColumns();
                $join       = $this->compileJoin();
                list($where, $params1) = $this->compileWhere();
                $groupBy    = $this->compileGroupBy();
                $orderBy    = $this->compileOrderBy();
                $having     = $this->compileHaving();
                list($union, $params2) = $this->compileUnion();
                $params3 = [];
                if(is_array($tableName)){
                    $params3 = $tableName[1];
                    $tableName = $tableName[0];
                }
                $queryString = "select {$columns} from {$tableName}{$join}{$where}{$groupBy}{$orderBy}{$having}{$union}";
                $params = array_merge($params1, $params2, $params3);
                break;
        }
        return [$queryString, $params];
    }

    /**
     * 处理表名
     * @param string|null $tableName
     * @return string|array
     */
    private function compileTable(?string $tableName = null): string|array
    {
        $table = $tableName ?? $this->query->tableName;
        if (is_array($table)) {
            $query = $table["query"];
            $as = $table["as"];
            list($queryString, $params) = $query->complie();
            return ["($queryString) ".($as ?? ""), $params];
        }
        $tablePrefix = Db::getTablePrefix();
        $table = preg_replace("/\s+/"," ",$table);
        $table = trim($table);
        if (empty($tablePrefix)) {
            if (stripos($table, " ") > 0) {
                $list = explode(" ", $table, 2);
                if (in_array($list[0], ["order", "group"])) {
                    return "`{$list[0]}`".(isset($list[1]) ? " {$list[1]}" : "");
                }
            } else {
                return "`{$table}`";
            }
        }
        return stripos($table, ".") || stripos($table, "`")>0 || stripos($table, "`")===0 ? $table : (($tablePrefix.$table));
    }

    /**
     * 处理字段
     * @return string
     */
    private function compileColumns(): string
    {
        $columnString = join(",", $this->query->columns);
        return str_replace(", ", ",", $columnString);
    }

    /**
     * 处理条件
     * @param bool $nested
     * @return array
     */
    public function compileWhere(bool $nested = false): array
    {
        $wheres = [];
        foreach ($this->query->wheres as $where) {
            switch ($where["type"]) {
                case "sub":
                    //子查询条件
                    if (isset($where["query"]) && $where["query"] instanceof Query) {
                        list($queryString, $params) = $where["query"]->compile();
                        $wheres[] = [
                            "where" => !empty($queryString) ? "({$queryString})" : "",
                            "or" => $where["or"] ?? false,
                            "params" => $params
                        ];
                    }
                    break;
                case "nested":
                    //嵌套查询条件
                    list($queryString, $params) = $where["query"]->getGrammar()->compileWhere(true);
                    $wheres[] = [
                        "where" => !empty($queryString) ? "({$queryString})" : "",
                        "or" => $where["or"] ?? false,
                        "params" => $params
                    ];
                    break;
                case "raw":
                    //原生SQL
                    $wheres[] = [
                        "where" => !empty($where["sql"]) ? $where["sql"] : "",
                        "or" => $where["or"] ?? false,
                        "params" => []
                    ];
                    break;
                case "in":
                    //where [not] in
                    if($where["value"] instanceof Query){
                        list($value, $params) = $where["value"]->compile();
                    }else{
                        $params = [];
                        $value = $where["value"];
                        $value = is_array($value) || is_object($value) ? $value : explode(",", $value);
                        $string = [];
                        foreach ($value as $key => $val){
                            if (is_numeric($val)) {
                                $string[] = $val;
                            } else {
                                $params[] = $val;
                                $string[] = "?";
                            }
                        }
                        $value = join(",", $string);
                    }
                    $where["column"] = Db::getConnection()->parseColumns($where["column"]);
                    $wheres[] = [
                        "where" => "{$where["column"]} ".($where["not"] ? "not " : "")."in ({$value})",
                        "or" => $where["or"],
                        "params" => $params
                    ];
                    break;
                case "between":
                    //where field [not] between A and B
                    $value = $where["value"];
                    $value = is_array($value) ? $value : explode(" and ", $value);
                    $params = [$value[0], $value[1]];
                    $value = "? and ?";
                    $where["column"] = Db::getConnection()->parseColumns($where["column"]);
                    $wheres[] = [
                        "where" => "{$where["column"]} ".($where["not"] ? "not " : "")."between {$value}",
                        "or" => $where["or"] ?? false,
                        "params" => $params
                    ];
                    break;
                case "exists":
                    //where  [not] exists (sql)
                    if (isset($where["query"]) && ($where["query"] instanceof Query)) {
                        list($queryString, $params) = $where["query"]->compile();
                        $wheres[] = [
                            "where" => ($where["not"] ? "not " : "")."exists ({$queryString})",
                            "or" => $where["or"] ?? false,
                            "params" => $params
                        ];
                    }
                    break;
                case "findInSet":
                    //where FIND_IN_SET(find, values)
                    $params = [];
                    if (is_numeric($where["value"]) || strripos($where["value"], ".")) {
                        $value = $where["value"];
                    } else {
                        $params[] = $where["value"];
                        $value = "?";
                    }
                    $where["column"] = Db::getConnection()->parseColumns($where["column"]);
                    $wheres[] = [
                        "where" => "find_in_set({$value}, {$where["column"]})",
                        "or" => $where["or"] ?? false,
                        "params" => $params
                    ];
                    break;
                case "like":
                    //where [not] like
                    $params = [];
                    $value = strripos($where["value"], "'") === 0 ? substr($where["value"], 1) : $where["value"];
                    $value = strrchr($value, "'") === "'" ? substr($value, -1) : $value;
                    $params[] = $where["side"] === "all" ? "%{$value}%" : ($where['side'] === "left" ? "{$value}%" : "%{$value}");
                    $value = "?";
                    $where["column"] = Db::getConnection()->parseColumns($where["column"]);
                    $wheres[] = [
                        "where" => "{$where["column"]} ".($where["not"] ? "not " : "")."like {$value}",
                        "or" => $where["or"] ?? false,
                        "params" => $params
                    ];
                    break;
                default:
                    //常规
                    $params = [];
                    if (is_numeric($where["value"])) {
                        $value = $where["value"];
                    } else {
                        $params[] = $where["value"];
                        $value = "?";
                    }
                    $where["column"] = Db::getConnection()->parseColumns($where["column"]);
                    $wheres[] = [
                        "where" => "{$where["column"]}{$where["operator"]}{$value}",
                        "or" => $where["or"] ?? false,
                        "params" => $params
                    ];
                    break;
            }
        }
        if (!empty($wheres)) {
            $whereString = $params = [];
            foreach ($wheres as $key => $w) {
                if (!empty($w["where"])) {
                    //条件 or / and
                    $boolean = $w["or"] ? "or" : "and";
                    //条件语句
                    $whereString[] = ($key === 0 ? "" : ((!empty($whereString[$key-1]) ? " " : "").$boolean." ")).$w["where"];
                    //参数化的值合并
                    if (!empty($w["params"])) {
                        $params = array_merge($params, $w["params"]);
                    }
                }
            }
            $whereSql = !empty($whereString) ? (($nested ? "" : " where ").join("", $whereString)) : null;
            return [$whereSql, $params];
        }
        return [null, []];
    }

    /**
     * 处理关联查询
     * @return string
     */
    private function compileJoin(): string
    {
        $string = [];
        foreach ($this->query->joins as $j) {
            if (!empty($j["table"])) {
                $table = $this->compileTable($j["table"]);
                $string[] = " {$j["type"]} join {$table}".($j["nested"] ? $j["as"] : "")." on {$j["on"]}";
            }
        }
        return join("", $string);
    }

    /**
     * 处理 set
     * @param string $type
     * @return array
     */
    private function compileSet(string $type) : array
    {
        switch($type){
            case "update" :
                //仅支持基础的一条语句更新
                $columns = $values = $params = [];
                $sets = is_array(end($this->query->sets)) ? end($this->query->sets) : $this->query->sets;
                foreach ($sets as $set) {
                    $columns[] = "`{$set["field"]}`=".($set["raw"] === true ? $set["value"] : (is_null($set["value"]) ? "NULL" : "?"));
                    if ($set["raw"] === false && !is_null($set["value"])) {
                        $params[] = $set["value"];
                    }
                }
                return [join(",",$columns), $params];
                break;
            case "insert" :
                //支持多条记录插入，但每条记录的字段必须一样
                $sets = is_array(end($this->query->sets)) ? $this->query->sets : [$this->query->sets];
                $columns = $values = $params = [];
                foreach ($sets as $key => $setArray) {
                    $thisColumns = $thisValues = $thisParams = [];
                    foreach ($setArray as $set) {
                        $thisColumns[] = "`{$set["field"]}`";
                        $thisValues[] = $set["raw"] === true ? $set["value"] : (is_null($set["value"]) ? "NULL" : "?");
                        if ($set["raw"] === false && !is_null($set["value"])) {
                            $thisParams[] = $set["value"];
                        }
                    }
                    $columns[] = "(".join(",", $thisColumns).")";
                    $values[] = "(".join(",", $thisValues).")";
                    $params[] = $thisParams;
                }
                return [$columns, $values, $params];
                break;
            case "insert where not exists" :
                //仅支持1条数据插入，请勿多条插入：insert into TABLE (f1, f2, fn) select "f1_val", "f2_val", "fn_val" from dual where not exists (select * from TABLE where {$where})
                $sets = is_array(end($this->query->sets)) ? end($this->query->sets) : $this->query->sets;
                $columns = $values = $params = [];
                foreach ($sets as $set) {
                    $columns[] = "`{$set["field"]}`";
                    $values[] = $set["raw"] === true ? $set["value"] : (is_null($set["value"]) ? "NULL" : "?");
                    if ($set["raw"] === false && !is_null($set["value"])) {
                        $params[] = $set["value"];
                    }
                }
                return ["(".join(",", $columns).")", join(",", $values), $params];
                break;
            default :
                return array("", [], []);
                break;
        }
    }


    /**
     * 解析 group by sql 语句
     * @return string
     */
    private function compileGroupBy() : string
    {
        return !empty($this->query->groupBy) ? " group by ".trim(join(",",$this->query->groupBy)) : "";
    }

    /**
     * 解析 order by sql 语句
     * @return string
     */
    private function compileOrderBy() : string
    {
        $string = [];
        foreach($this->query->orderBy as $o){
            if(!empty($o["column"])){
                $o["column"] = Db::getConnection()->parseColumns($o["column"]);
                $string[] = preg_replace("/\s+/","",$o["column"])." ".strtolower($o["type"]);
            }
        }
        return !empty($string) ? (" order by ".join(",",$string)) : "";
    }

    /**
     * 解析 HAVING 侧重
     * @return string
     */
    private function compileHaving() : string
    {
        $string = [];
        foreach ($this->query->having as $h) {
            if (!empty($h["column"])) {
                $h["column"] = Db::getConnection()->parseColumns($h["column"]);
                $string[] = $h["column"].$h["operator"].$h["value"];
            }
        }
        return !empty($string) ? " having ".join(",", $string) : "";
    }

    /**
     * 解析 UNION 联合查询
     * @return array
     */
    private function compileUnion() : array
    {
        $unions = $this->query->unions;
        $string = $params = [];
        foreach ($unions as $u) {
            list($queryString, $paramsList) = $u["query"]->compile();
            if (!empty($queryString)) {
                $string[] = "union ".($u["all"] ? "all " : "")."({$queryString})";
                if (!empty($params)) {
                    $params = array_merge($params, $paramsList);
                }
            }
        }
        $queryString = !empty($string) ? " ".join(" ",$string) : "";
        return [$queryString, $params];
    }
}